Investigation on Population Migration within Cologne¶
Jupyter Notebook in Python Language for pursuing the Analysis
All data is gathered from https://www.offenedaten-koeln.de
For best experience, please use the Firefox Browser
Notebook Preparations¶
First, we need to import the necessary libraries
# Data tables will be handled by the pandas library
import pandas as pd
# The source data will be given in (geo-)json format.
# We need the json library to quickly parse the raw data into python dictionaries
import json
# Import libraries for visalization
import folium # Map visualizations
import matplotlib.cm as cm # Colormaps
import matplotlib.colors as colors
import matplotlib.pyplot as plt # Plotting
import seaborn as sns
# Import libraries for html requests
import requests # Will be used to request data from Foursquare
import geocoder # Will be used to locate cologne WGS84 coordinates
from geopy.geocoders import Nominatim # Will be used to locate cologne WGS84 coordinates
# Data handling and ML algorithms
import numpy as np
from sklearn.cluster import KMeans # KMeans Algorithm
from sklearn import tree # Decision Tree Algorithm
import sklearn.metrics # Evaluation
from sklearn import preprocessing # Data Preparation
from sklearn.model_selection import train_test_split
(1) Get Venue Data for Cologne's Districts¶
First, we want to perform a similar analysis like we did in the previous capstone project assignments for NY and Toronto. Therefore, we need a data set containing Cologne's districts with center points around which we can search for venues by using Foursquare API calls.
Data Preprocessing¶
# Open GeoJson File of Cologne
with open('bezirke.json', encoding='utf8') as f:
data = json.load(f)
# What attributes are available:
print(data['features'][0]['attributes'].keys())
We see that the file is given in geojson format. In the attribute's section of each item, we see several keys. Here, we will use NAME which gives us the name of the district.
Let's look at the districts given in the file:
# Look at all the neighborhoods
print([x['attributes']['NAME'] for x in data['features']])
Next, we want to retrieve the center points of each district. Unfortunately, they are not given in the file. What we have, however, is the polygon edges of each district.
For instance, let's look at the first 5 points of of district Nippes:
[x['geometry']['rings'] for x in data['features'] if x['attributes']['NAME'] == 'Nippes'][0][0][0:5]
The following function computes the centroid of a polygon given its edge points:
# Define a function to find a centroid from a polygon
def centroid(vertexes):
_x_list = [vertex [0] for vertex in vertexes]
_y_list = [vertex [1] for vertex in vertexes]
_len = len(vertexes)
_x = sum(_x_list) / _len
_y = sum(_y_list) / _len
return(_x, _y)
Let's use that function to find the centroid for Nippes:
# For example: center of district 'Nippes'
district = 'Nippes'
centroid([x['geometry']['rings'] for x in data['features'] if x['attributes']['NAME'] ==district][0][0])
Let's generalize that procedure to find the centroid for a given district name:
def get_centroid_for_district(name):
return centroid([x['geometry']['rings'] for x in data['features'] if x['attributes']['NAME'] == name][0][0])
Now, we have all ingredients to create a pandas data frame from the json data. We want to extract each district's name, center latitude, center longitude and the list of polygon edge points.
# Create a new data frame
df_cologne = pd.DataFrame(columns=['District', 'Latitude', 'Longitude', 'Polygon'])
# Extract the information from the file
df_cologne['District'] = [x['attributes']['NAME'] for x in data['features']]
df_cologne['Latitude'] = df_cologne['District'].apply(get_centroid_for_district).apply(lambda i: i[1])
df_cologne['Longitude'] = df_cologne['District'].apply(get_centroid_for_district).apply(lambda i: i[0])
df_cologne['Polygon'] = df_cologne['District'].apply(lambda d: [x['geometry']['rings'] for x in data['features'] if x['attributes']['NAME'] == d][0][0])
df_cologne.head()
Creating a Cologne Map¶
Now that we have the districts' positions, let's create a request to Nominatim to get Cologne's center point in order to center the map around it:
address = 'Cologne, DE'
geolocator = Nominatim(user_agent="cologne_explorer")
location = geolocator.geocode(address)
latitude = location.latitude
longitude = location.longitude
print('The geograpical coordinate of Cologne are {}, {}.'.format(latitude, longitude))
Next, we create a map centered around Cologne with all the districts' center points from the data frame created above.
# create map and display it
cologne_map = folium.Map(location=[latitude, longitude], zoom_start=12)
# instantiate a feature group for the incidents in the dataframe
districts = folium.map.FeatureGroup()
# loop through the 100 crimes and add each to the incidents feature group
for lat, lng, label in zip(df_cologne.Latitude, df_cologne.Longitude, df_cologne.District):
districts.add_child(
folium.CircleMarker(
[lat, lng],
radius=5, # define how big you want the circle markers to be
color='black',
fill=True,
fill_color='white',
fill_opacity=0.6,
popup=label
)
)
# add incidents to map
cologne_map.add_child(districts)
Explore the Districts with Foursquare API¶
The map above looks good. Now, we want to get venue data for each district. Therefore, similar to the analyses done before, we create requests for each district's location to Foursquare using our credentials.
CLIENT_ID = 'K0OBBE3XME1ORY4POM5AWPU1BI01AGX01IIDL5EL4D005UA4' # your Foursquare ID
CLIENT_SECRET = 'APU0I3ENTYA0Z2NHBT1T3RREEH2GBPRF5AHSFYCZVB4TCU1O' # your Foursquare Secret
VERSION = '20180605' # Foursquare API version
We recycle the function from the assignment to get venues in a certain radius around a location:
def getNearbyVenues(names, latitudes, longitudes, radius=500):
venues_list=[]
for name, lat, lng in zip(names, latitudes, longitudes):
print(name)
# create the API request URL
url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(
CLIENT_ID,
CLIENT_SECRET,
VERSION,
lat,
lng,
radius,
LIMIT)
# make the GET request
results = requests.get(url).json()["response"]['groups'][0]['items']
# return only relevant information for each nearby venue
venues_list.append([(
name,
lat,
lng,
v['venue']['name'],
v['venue']['location']['lat'],
v['venue']['location']['lng'],
v['venue']['categories'][0]['name']) for v in results])
nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
nearby_venues.columns = ['Neighborhood',
'Neighborhood Latitude',
'Neighborhood Longitude',
'Venue',
'Venue Latitude',
'Venue Longitude',
'Venue Category']
return(nearby_venues)
Let's perform the request with a limit of 100 venues and a radius of 500:
LIMIT = 100
cologne_venues = getNearbyVenues(names=df_cologne['District'],
latitudes=df_cologne['Latitude'],
longitudes=df_cologne['Longitude'],
radius=500)
The venues are saved in the following data frame:
cologne_venues.head()
As a next step, we convert the table in a one hot encoded table using pandas' get_dummies method. This makes it possible to apply machine learning algorithms on categorical data.
# Clustering to see similar districts in cologne
# one hot encoding
cologne_onehot = pd.get_dummies(cologne_venues[['Venue Category']], prefix="", prefix_sep="")
# add neighborhood column back to dataframe
cologne_onehot['Neighborhood'] = cologne_venues['Neighborhood']
# move neighborhood column to the first column
fixed_columns = [cologne_onehot.columns[-1]] + list(cologne_onehot.columns[:-1])
cologne_onehot = cologne_onehot[fixed_columns]
cologne_onehot.head()
We group the data by district and use the mean method to get a measure for the ratio of venues of a certain category in that district:
cologne_grouped = cologne_onehot.groupby('Neighborhood').mean().reset_index()
cologne_grouped.head()
Let's now - as an additional information - look at the top 3 venues in each district:
num_top_venues = 3
for hood in cologne_grouped['Neighborhood']:
print("{:-^30}".format(hood))
temp = cologne_grouped[cologne_grouped['Neighborhood'] == hood].T.reset_index()
temp.columns = ['venue','freq']
temp = temp.iloc[1:]
temp['freq'] = temp['freq'].astype(float)
temp = temp.round({'freq': 2})
print(temp.sort_values('freq', ascending=False).reset_index(drop=True).head(num_top_venues))
print('\n')
The following function generalizes this behavior and gives us the top x venues in a certain district. This will then be used to get for each district the top x venues in a data table.
def return_most_common_venues(row, num_top_venues):
row_categories = row.iloc[1:]
row_categories_sorted = row_categories.sort_values(ascending=False)
return row_categories_sorted.index.values[0:num_top_venues]
num_top_venues = 10
indicators = ['st', 'nd', 'rd']
# create columns according to number of top venues
columns = ['Neighborhood']
for ind in np.arange(num_top_venues):
try:
columns.append('{}{} Most Common Venue'.format(ind+1, indicators[ind]))
except:
columns.append('{}th Most Common Venue'.format(ind+1))
# create a new dataframe
neighborhoods_venues_sorted = pd.DataFrame(columns=columns)
neighborhoods_venues_sorted['Neighborhood'] = cologne_grouped['Neighborhood']
for ind in np.arange(cologne_grouped.shape[0]):
neighborhoods_venues_sorted.iloc[ind, 1:] = return_most_common_venues(cologne_grouped.iloc[ind, :], num_top_venues)
neighborhoods_venues_sorted[neighborhoods_venues_sorted.Neighborhood == "Nippes"]
Now we can see, that for instance in Nippes, there are many places for meals and drinks.
Clustering of Venues¶
In the next step, we try to find similarities between districts according to their most common venues. For this, we use k-means clustering.
# set number of clusters
kclusters = 10
cologne_grouped_clustering = cologne_grouped.drop('Neighborhood', 1)
for i in cologne_grouped_clustering.columns:
cologne_grouped_clustering[i] = preprocessing.minmax_scale(cologne_grouped_clustering[i].astype('float64'))
# run k-means clustering
kmeans = KMeans(n_clusters=kclusters, random_state=0).fit(cologne_grouped_clustering)
The kmeans algorithm has no assigned a cluster id to each district. Let's merge this data with the original data frame:
# add clustering labels
neighborhoods_venues_sorted.insert(0, 'Cluster Labels', kmeans.labels_)
cologne_merged = df_cologne
# merge toronto_grouped with toronto_data to add latitude/longitude for each neighborhood
cologne_merged = cologne_merged.join(neighborhoods_venues_sorted.set_index('Neighborhood'), on='District')
cologne_merged
For some districts there was no data received apparently from Foursquare. Let's just remove those districts from the analysis. Also, after removing the NaNs we can convert the cluster label column to integer type.
cologne_merged.dropna(inplace=True)
cologne_merged['Cluster Labels'] = cologne_merged['Cluster Labels'].astype(int)
Let's now visualize the result on a map:
# create map
map_clusters = folium.Map(location=[latitude, longitude], zoom_start=11)
# set color scheme for the clusters
x = np.arange(kclusters)
ys = [i + x + (i*x)**2 for i in range(kclusters)]
colors_array = cm.rainbow(np.linspace(0, 1, len(ys)))
rainbow = [colors.rgb2hex(i) for i in colors_array]
# add markers to the map
markers_colors = []
for lat, lon, poi, cluster in zip(cologne_merged['Latitude'], cologne_merged['Longitude'], cologne_merged['District'], cologne_merged['Cluster Labels']):
label = folium.Popup(str(poi) + ' Cluster ' + str(cluster), parse_html=True)
folium.CircleMarker(
[lat, lon],
radius=5,
popup=label,
color=rainbow[cluster-1],
fill=True,
fill_color=rainbow[cluster-1],
fill_opacity=0.7).add_to(map_clusters)
map_clusters
We can see the different clusters of the Cologne venue environment. One interpretation of the result could be that in the city center the venue distribution is very heterogeneous. The clusters are not similar to any other cluster. Each district in the city center forms its own cluster and thus does not resemble any other district in the Cologne area. If we go to the outer rings, we see more similarities between districts. For instance, the districts close to but right of the Rhein river seem to be quite similar (cluster 6). They also show similarties to clusters on the left side of the rivers, in districts where industry governed. If we look at cluster 3, we again see a lot of food and drink venues but now in form of restaurants and also shops. This could be an area with many households. Cluster 1 shows many activity locations.
cologne_merged[cologne_merged['Cluster Labels'] == 3].head()
cologne_merged[cologne_merged['Cluster Labels'] == 1].head()
This concludes the section about Cologne's venue environment analysis. There are some hints about whether or not people might want to migrate to other districts but for sure further analyses have to be performed.
(2) Get Area Usage Data¶
Next we look at the usage of space within the different districts. For this, we have the following data set:
# Open GeoJson File of Cologne
with open('flaeche.json', encoding='utf8') as f:
data = json.load(f)
Again, we have a geojson file which has attributes about different space usage categories of the districts. We want to extract only a few in here. These are:
- FN_BEBAUT_AP : Ratio of built-up area
- FN_PARK_AP : Ratio of park area
- FN_VERKEHR_AP : Ratio of area assigned to traffic
- FN_WASSER_AP : Area ratio of water
df_cologne_area = pd.DataFrame(columns=['District','built-up', 'park', 'traffic', 'water'])
for district in data['features']:
df_cologne_area = df_cologne_area.append(
{
'District': district['properties']['NAME'],
'built-up': district['properties']['FN_BEBAUT_AP'],
'park': district['properties']['FN_PARK_AP'],
'traffic': district['properties']['FN_VERKEHR_AP'],
'water': district['properties']['FN_WASSER_AP']
}, ignore_index=True
)
df_cologne_area.head()
Cluster analysis¶
After we have a data frame containing the ratios of different area usage categories, we can again try to find clusters but this time considering area usages. As the process is similar as above for the venues, I won't further explain the process.
# set number of clusters
kclusters = 6
# Normalize the data by dividing by 100 (as they are percent values)
c_area_grouped_clustering = df_cologne_area.drop('District', 1) / 100
# run k-means clustering
kmeans = KMeans(n_clusters=kclusters, random_state=0).fit(c_area_grouped_clustering)
# add clustering labels
df_cologne_area_with_lables = df_cologne_area.copy()
df_cologne_area_with_lables.insert(len(df_cologne_area_with_lables.columns), 'Area Cluster Labels', kmeans.labels_)
df_cologne_area_with_lables.head()
df_cologne_merged_area = df_cologne_area_with_lables.merge(df_cologne[['District', 'Latitude', 'Longitude']], on='District', how='outer')
df_cologne_merged_area.head()
Let's also check if we have any NaN in the frame:
df_cologne_merged_area[df_cologne_merged_area.isna().any(axis=1)]
As there are no NaNs, we can directly plot the data in a map:
# create map
map_clusters_area = folium.Map(location=[latitude, longitude], zoom_start=11)
# set color scheme for the clusters
x = np.arange(kclusters)
ys = [i + x + (i*x)**2 for i in range(kclusters)]
colors_array = cm.rainbow(np.linspace(0, 1, len(ys)))
rainbow = [colors.rgb2hex(i) for i in colors_array]
# add markers to the map
markers_colors = []
for lat, lon, poi, cluster in zip(df_cologne_merged_area['Latitude'], df_cologne_merged_area['Longitude'], df_cologne_merged_area['District'], df_cologne_merged_area['Area Cluster Labels']):
label = folium.Popup(str(poi) + ' Cluster ' + str(cluster), parse_html=True)
folium.CircleMarker(
[lat, lon],
radius=5,
popup=label,
color=rainbow[cluster-1],
fill=True,
fill_color=rainbow[cluster-1],
fill_opacity=0.7).add_to(map_clusters_area)
map_clusters_area
As expected we see the distribution of clusters matching the open street map's color code. Areas which are close to rivers belong to one cluster category and then the clusters are ring-shaped with their common center being the city center. This makes again sense, since built-up areas and traffic decreases radially. Let's try to use a Choropleth map to make this more apparent:
# Open GeoJson File of Cologne without encoding
with open('flaeche.json') as f:
data = json.load(f)
df_cologne_area_encNone = pd.DataFrame(columns=['District','built-up', 'park', 'traffic', 'water'])
for district in data['features']:
df_cologne_area_encNone = df_cologne_area_encNone.append(
{
'District': district['properties']['NAME'],
'built-up': district['properties']['FN_BEBAUT_AP'],
'park': district['properties']['FN_PARK_AP'],
'traffic': district['properties']['FN_VERKEHR_AP'],
'water': district['properties']['FN_WASSER_AP']
}, ignore_index=True
)
from folium import plugins
cc_map = folium.Map(location=[latitude, longitude],zoom_start=12)
folium.Choropleth(
geo_data='flaeche.json',
name='Water',
data=df_cologne_area_encNone,
columns=['District', 'water'],
key_on='feature.properties.NAME',
fill_color='YlGnBu',
fill_opacity=0.7,
line_opacity=0.2,
legend_name='Water Land Use in %'
).add_to(cc_map)
cc_map
In this Choropleth map we see the water density in the certain districts. This semi-explains the distribution of clusters. Let's also look at the built-up density:
from folium import plugins
cc_map = folium.Map(location=[latitude, longitude],zoom_start=12)
folium.Choropleth(
geo_data='flaeche.json',
name='Built-up',
data=df_cologne_area_encNone,
columns=['District', 'built-up'],
key_on='feature.properties.NAME',
fill_color='YlGnBu',
fill_opacity=0.7,
line_opacity=0.2,
legend_name='Built-up Land Use in %'
).add_to(cc_map)
cc_map
Here, we see how built-up areas are more dense towards the city center, but we also find some dense areas towards the south-east near the airport. This could - for instance - explain, why districts in that area fall into the same cluster label class as districts in the city center.
(3) Households¶
In the following, we use the very same analysis as for the area usage, but now for the distribution of households. The data has the same format, i.e. the same processes are used to extract the following columns:
- District: Name of district
- density: Density of households per ha
- ratio-xP: Ratio of people living in households of x persons
- ratio-5P+: Ratio of people living in households of 5 persons or above
# Open GeoJson File of Cologne
with open('haushalte.json', encoding='utf8') as f:
data = json.load(f)
df_cologne_household = pd.DataFrame(columns=['District','density', 'ratio-1P', 'ratio-2P', 'ratio-3P', 'ratio-4P', 'ratio-5P+'])
for district in data['features']:
df_cologne_household = df_cologne_household.append(
{
'District': district['properties']['NAME'],
'density': district['properties']['HHD_AP'],
'ratio-1P': district['properties']['HH_HG_1PERS_AP'],
'ratio-2P': district['properties']['HH_HG_2PERS_AP'],
'ratio-3P': district['properties']['HH_HG_3PERS_AP'],
'ratio-4P': district['properties']['HH_HG_4PERS_AP'],
'ratio-5P+': district['properties']['HH_HG_AB5PERS_AP'],
}, ignore_index=True
)
df_cologne_household.head()
Again, using clustering, we retrieve a map showing the cluster distribution by household:
# set number of clusters
kclusters = 5
c_hh_grouped_clustering = df_cologne_household.drop('District', 1) / 100
# run k-means clustering
kmeans = KMeans(n_clusters=kclusters, random_state=0).fit(c_hh_grouped_clustering)
# add clustering labels
df_cologne_hh_with_lables = df_cologne_household.copy()
df_cologne_hh_with_lables.insert(len(df_cologne_hh_with_lables.columns), 'HH Cluster Labels', kmeans.labels_)
df_cologne_hh_with_lables.head()
df_cologne_merged_hh = df_cologne_hh_with_lables.merge(df_cologne[['District', 'Latitude', 'Longitude']], on='District', how='outer')
df_cologne_merged_hh.head()
# create map
map_clusters_hh = folium.Map(location=[latitude, longitude], zoom_start=11)
# set color scheme for the clusters
x = np.arange(kclusters)
ys = [i + x + (i*x)**2 for i in range(kclusters)]
colors_array = cm.rainbow(np.linspace(0, 1, len(ys)))
rainbow = [colors.rgb2hex(i) for i in colors_array]
# add markers to the map
markers_colors = []
for lat, lon, poi, cluster in zip(df_cologne_merged_hh['Latitude'], df_cologne_merged_hh['Longitude'], df_cologne_merged_hh['District'], df_cologne_merged_hh['HH Cluster Labels']):
label = folium.Popup(str(poi) + ' Cluster ' + str(cluster), parse_html=True)
folium.CircleMarker(
[lat, lon],
radius=5,
popup=label,
color=rainbow[cluster-1],
fill=True,
fill_color=rainbow[cluster-1],
fill_opacity=0.7).add_to(map_clusters_hh)
map_clusters_hh
Here, it is very obvious, that there is one cluster which contains the outer areas of Cologne. Let's look at it's shape:
df_cologne_merged_hh[df_cologne_merged_hh['HH Cluster Labels'] == 1].iloc[:,2:7].mean()
We see, that the household distribution is rather equal. This speaks for larger families living in these areas. Compared to households near the city center, we see the difference:
df_cologne_merged_hh[df_cologne_merged_hh['HH Cluster Labels'] == 2].iloc[:,2:7].mean()
Lastly, let's visualize the density of households on a Choropleth map:
# Open GeoJson File of Cologne without encoding
with open('haushalte.json') as f:
data = json.load(f)
df_cologne_hh_encNone = pd.DataFrame(columns=['District','built-up', 'park', 'traffic', 'water'])
for district in data['features']:
df_cologne_hh_encNone = df_cologne_hh_encNone.append(
{
'District': district['properties']['NAME'],
'density': district['properties']['HHD_AP'],
'ratio-1P': district['properties']['HH_HG_1PERS_AP'],
'ratio-2P': district['properties']['HH_HG_2PERS_AP'],
'ratio-3P': district['properties']['HH_HG_3PERS_AP'],
'ratio-4P': district['properties']['HH_HG_4PERS_AP'],
'ratio-5P+': district['properties']['HH_HG_AB5PERS_AP'],
}, ignore_index=True
)
from folium import plugins
cc_map = folium.Map(location=[latitude, longitude],zoom_start=12)
folium.GeoJson('haushalte.json').add_to(cc_map)
folium.Choropleth(
geo_data='haushalte.json',
name='Household Density',
data=df_cologne_hh_encNone,
columns=['District', 'density'],
key_on='feature.properties.NAME',
fill_color='YlOrRd',
fill_opacity=0.7,
line_opacity=0.2,
legend_name='Household Density /ha'
).add_to(cc_map)
cc_map
This result matches the expectation as districts which show higher amounts of single person households will also show a higher density of households.
(4) Age Distribution¶
Lastly, we look at the age distribution in the districts. We have the data in .csv format and can easily scrape them.
df_cologne_age = pd.read_csv('altersgruppen.csv', sep=';')
df_cologne_age.head()
Do some cosmetics:
df_cologne_age.drop(['Nr.'], axis=1, inplace=True)
df_cologne_age.rename(columns={'Einwohner insgesamt': 'Total', 'Stadtteil': 'District', '80 und älter': '80+'}, inplace=True)
# Normalize values
df_cologne_age_relative = df_cologne_age[df_cologne_age.columns[2:]].apply(lambda c: c/df_cologne_age.Total)
df_cologne_age_relative['District'] = df_cologne_age['District']
# Move District-column to beginning of table
fixed_columns = list(df_cologne_age_relative.columns[-1:]) + list(df_cologne_age_relative.columns[:-1])
df_cologne_age_relative = df_cologne_age_relative[fixed_columns]
df_cologne_age_relative.head()
And perform again the cluster analysis:
# set number of clusters
kclusters = 5
c_age_grouped_clustering = df_cologne_age_relative.drop('District', 1)
# run k-means clustering
kmeans = KMeans(n_clusters=kclusters, random_state=0).fit(c_age_grouped_clustering)
# add clustering labels
df_cologne_age_with_lables = df_cologne_age_relative.copy()
df_cologne_age_with_lables.insert(len(df_cologne_age_with_lables.columns), 'Age Cluster Labels', kmeans.labels_)
df_cologne_age_with_lables.head()
df_cologne_merged_age = df_cologne_age_with_lables.merge(df_cologne[['District', 'Latitude', 'Longitude']], on='District')
# create map
map_clusters_age = folium.Map(location=[latitude, longitude], zoom_start=11)
# set color scheme for the clusters
x = np.arange(kclusters)
ys = [i + x + (i*x)**2 for i in range(kclusters)]
colors_array = cm.rainbow(np.linspace(0, 1, len(ys)))
rainbow = [colors.rgb2hex(i) for i in colors_array]
# add markers to the map
markers_colors = []
for lat, lon, poi, cluster in zip(df_cologne_merged_age['Latitude'], df_cologne_merged_age['Longitude'], df_cologne_merged_age['District'], df_cologne_merged_age['Age Cluster Labels']):
label = folium.Popup(str(poi) + ' Cluster ' + str(cluster), parse_html=True)
folium.CircleMarker(
[lat, lon],
radius=5,
popup=label,
color=rainbow[cluster-1],
fill=True,
fill_color=rainbow[cluster-1],
fill_opacity=0.7).add_to(map_clusters_age)
map_clusters_age
We see again radial clustering. But here, we can see a clearer distinction than before. Let's make this distinction visible at one example: Note how all the districts in the center of Cologne (horizontal center) are in the same cluster class (cluster 0). Please look at the age distribution there:
df_cologne_merged_age[df_cologne_merged_age['Age Cluster Labels'] == 0].iloc[:,1:12].mean()*100
In comparison let's look at the age distributions from two outer cluster families:
df_cologne_merged_age[df_cologne_merged_age['Age Cluster Labels'] == 1].iloc[:,1:12].mean()*100
df_cologne_merged_age[df_cologne_merged_age['Age Cluster Labels'] == 4].iloc[:,1:12].mean()*100
We see that in the center live people in working age. This may be because, it's rather expensive to live there but at the same time it's a very busy place. People who get older, or who want to raise children move more to the outer districts. Which is visible from the higher amount of people in age 6-14 (children at school) and 65-74 (retired).
(5) Putting everything together¶
Finally we want to look at the migration between districts. In the last sections we have prepared the following data tables:
df_cologne_merged_age.head(0)
df_cologne_merged_area.head(0)
df_cologne_merged_hh.head(0)
cologne_merged.head(0)
Let's now merge the cluster analyses' results into one frame:
df_cologne_result = cologne_merged[['District', 'Latitude', 'Longitude', 'Cluster Labels']].merge(df_cologne_merged_age[['District','Age Cluster Labels']], on='District')
df_cologne_result = df_cologne_result.merge(df_cologne_merged_area[['District', 'Area Cluster Labels']], on='District')
df_cologne_result = df_cologne_result.merge(df_cologne_merged_hh[['District', 'HH Cluster Labels']], on='District')
df_cologne_result.rename(columns={'Cluster Labels': 'Venue Cluster Labels'}, inplace=True)
df_cologne_result.head()
Now, we add a data file which contains data about the net migration within a district, i.e. if it's > 0, we have a growing population. Let's encode that fact in a new variable: Churn:
# Open GeoJson File of Cologne without encoding
with open('einwohner.json') as f:
data = json.load(f)
y = pd.DataFrame(columns=['District','Churn'])
for district in data['features']:
y = y.append({
'District': district['properties']['NAME'],
'Churn': district['properties']['SALDO_BINNEN_AA'] > 0
}, ignore_index=True)
df_cologne_result = df_cologne_result.merge(y, on='District')
df_cologne_result['Churn'] = df_cologne_result['Churn'].map({True: 1, False: 0})
df_cologne_result.head()
This is our data set. We split it into a training and test set and model a decision tree out of it.
X = df_cologne_result.iloc[:,3:-1]
Y = df_cologne_result['Churn']
X_train, X_test, y_train, y_test = train_test_split( X, Y, test_size=0.4, random_state=4)
clf = tree.DecisionTreeClassifier(criterion='entropy', max_depth=3 )
clf = clf.fit(X_train, y_train)
Unfortunately, we have a rather low f1 and jaccard score. Probably, we'd need more data to get better results. Therefore, it would be good to make the analysis more granular, i.e. have more datapoints per district.
print('Decision Tree F1-Score: {:.2f}'.format(sklearn.metrics.f1_score(y_test, clf.predict(X_test), average='weighted')))
print('Decision Tree Jaccard-Score: {:.2f}'.format(sklearn.metrics.jaccard_similarity_score(y_test, clf.predict(X_test))))
Nevertheless, let's look at the resulting tree:
import graphviz
dot_data = tree.export_graphviz(
clf,
feature_names=X.columns.values,
class_names=list(map(str,range(max(Y)+1))),
filled=True, rounded=True, precision=0,
out_file=None)
graph = graphviz.Source(dot_data, format='svg')
graph.render(filename='figs/decision')
graph
By looking at the top features of the decision tree, we see which categories seem to have the biggest impact on whether or not a district is growing or shrinking.
df_topfeatures = pd.DataFrame({'Feature': X.columns.values, 'Importance':clf.feature_importances_}).sort_values(by='Importance', ascending=False).head().reset_index()
df_topfeatures
This is, that primarily, the age distribution govers, if a district is growing or shrinking. Let's therefore look at the age cluster labels that mean a high/low churn:
sns.boxplot(data=df_cologne_result, x='Age Cluster Labels', y='Churn')
We see that for instance districts with age cluster 0 labels have a shrinking population. We remember, these where the ones in the city center, where most probably, costs of living are very high and where we had a high ratio of younger people living in 1Person households. As soon as the found a family, they will move to outer district, i.e. this makes sense. If we look for instance at age cluster 3:
df_cologne_merged_age[df_cologne_merged_age['Age Cluster Labels'] == 3].iloc[:,1:12].mean()*100
We see, that in those districts, older people are living that have already a family or they are retired. In both cases they probably have a lower interest in leaving their place, i.e. they stay while people from other districts enter.